﻿using Abp.Application.Services.Dto;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;

namespace ZCMS.Dapper
{
	public static class Paged
	{
		private static string _getPagedListSql;
		private static string GetPageSql(IDbConnection connection)
		{
			var type = connection.GetType();
			switch (type.Name)
			{
				case "OracleConnection":
					_getPagedListSql = "select* from (select /*+ first_rows({RowsPerPage}) */ {SelectColumns}, row_number()  over(order by {OrderBy} )rn from {TableName} {WhereClause}) tt" +
										"where rn between (({PageNumber}-1) * {RowsPerPage} + 1) and {PageNumber} * {RowsPerPage} order by rn";
					break;
				case "MySQLConnection":
					_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {Offset},{RowsPerPage}";
					break;
				case "SQLiteConnection":
					_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage})";
					break;
				case "PostgreSQLConnection":
					_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage})";
					break;
				default:
					_getPagedListSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNUMBER BETWEEN (({PageNumber}-1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage})";
					break;
			}
			return _getPagedListSql;
		}


		public static PagedResultDto<T> GetListPaged<T>(this IDbConnection connection, int pageIndex, int pageSize, string tableName, string fileds, string conditions, string orderby, object parameters = null) where T : class
		{
			var TotalCount = RecordCount(connection, tableName, conditions, parameters);
			var items = GetPageList<T>(connection, pageIndex, pageSize, tableName, fileds, conditions, orderby, parameters).AsList();
			return new PagedResultDto<T>(TotalCount, items);
		}


		private static IEnumerable<T> GetPageList<T>(IDbConnection connection, int pageNumber, int rowsPerPage, string tableName, string fileds, string conditions, string orderby, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null)
		{
			var _getPagedListSql = GetPageSql(connection);

			if (string.IsNullOrEmpty(_getPagedListSql))
				throw new Exception("GetListPage is not supported with the current SQL Dialect");

			if (pageNumber < 1)
				throw new Exception("Page must be greater than 0");

			if (string.IsNullOrEmpty(orderby))
			{
				throw new Exception("Page must have orderby");
			}

			var currenttype = typeof(T);

			if (!conditions.ToLower().Contains("where"))
			{
				conditions = "where " + conditions;
			}

			var query = _getPagedListSql;
			query = query.Replace("{SelectColumns}", fileds);
			query = query.Replace("{TableName}", tableName);
			query = query.Replace("{PageNumber}", pageNumber.ToString());
			query = query.Replace("{RowsPerPage}", rowsPerPage.ToString());
			query = query.Replace("{OrderBy}", orderby);
			query = query.Replace("{WhereClause}", conditions);
			query = query.Replace("{Offset}", ((pageNumber - 1) * rowsPerPage).ToString());

			if (Debugger.IsAttached)
				Trace.WriteLine(String.Format("GetListPaged<{0}>: {1}", currenttype, query));

			return connection.Query<T>(query, parameters, transaction, true, commandTimeout);
		}

		private static int RecordCount(IDbConnection connection, string tableName, string conditions = "", object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null)
		{
			var sb = new StringBuilder();
			sb.Append("Select count(1)");
			sb.AppendFormat(" from {0}", tableName);

			if (!string.IsNullOrWhiteSpace(conditions))
			{
				if (!conditions.ToLower().Contains("where"))
				{
					sb.Append(" where ");
				}
			}
			sb.Append(" " + conditions);

			if (Debugger.IsAttached)
				Trace.WriteLine(String.Format("RecordCount<{0}>: {1}", "", sb));

			return connection.ExecuteScalar<int>(sb.ToString(), parameters, transaction, commandTimeout);
		}
	}
}
